package main.java.com.fxy.pojo;

import lombok.Data;
import main.java.com.fxy.util.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Data
public class Page<T> {
    private Integer currentPage; // 当前页数
    private Integer totalPage; // 总页数
    private Integer totalSize; // 总记录数
    private Integer pageSize; // 每页记录数
    private List<T> list; // 当前页数据 <T>

    public Integer getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(Integer currentPage) {
        this.currentPage = currentPage;
    }

    public Integer getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(Integer totalPage) {
        this.totalPage = totalPage;
    }

    public Integer getTotalSize() {
        return totalSize;
    }

    public void setTotalSize(Integer totalSize) {
        this.totalSize = totalSize;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    // 分页总数
    public int size(int id) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = DBUtils.getConn();
            String sql = "select count(*) from cost where user_id ='" + id +"'";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            if(rs.next()) {
                return rs.getInt(1);
            }
        } finally {
            DBUtils.close(rs, st, conn);
        }
        return 0;
    }

    // 分页查询数据
    public List<Cost> queryAllByLimit(int offset, int limit,int userId) throws SQLException {
        String sql ="select * from cost where user_id = '"+userId+"' "+ "limit " + offset + "," + limit;
        List<Cost> list = new ArrayList<>();
        try {
            ResultSet rs = DBUtils.getConn().prepareStatement(sql).executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");//获取查询结果中的id
                String name = rs.getString("name");//获取查询结果中的name
                Double money = rs.getDouble("money");//获取查询结果中的money
                Date date = rs.getDate("date"); //获取查询结果中的date

                Cost cost = new Cost(id,name,money,date);//调用构造方法赋值
                list.add(cost);//添加到list集合中
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    // 分页查询处理
    public void selectUserListByPage(Integer currentPage, Page<Cost> page,int id,int pageSize) throws SQLException {
        // 当前页数
        page.setCurrentPage(currentPage);
        // 总记录数
        Integer totalSize = page.size(id);
        System.out.println(totalSize);
        page.setTotalSize(totalSize);
        // 每页记录数
        page.setPageSize(pageSize);
        // 总页数(没余数为整页，有余数页数+1)
        Integer totalPages = null;
        if (totalSize != null) {
            totalPages = (totalSize%pageSize==0) ? (totalSize/pageSize) : (totalSize/pageSize+1);
        }
        if(totalPages != null) {
            page.setTotalPage(totalPages);
        }
        // 当前页数据
        List<Cost> list = queryAllByLimit(pageSize*(currentPage-1), pageSize,id);
        page.setList(list);
    }

    public void selectUserListByKeyword(Integer currentPage, Page<Cost> page, int userId, String keyword,int pageSize) throws SQLException {
        System.out.println("3");
        page.setCurrentPage(currentPage);
        Integer totalSize = sizeByKeyword(userId, keyword);
        page.setTotalSize(totalSize);
        page.setPageSize(pageSize);
        int totalPages = (int) Math.ceil(totalSize / (double) pageSize);
        page.setTotalPage(totalPages);
        int offset = pageSize * (currentPage - 1);
        System.out.println("6");
        List<Cost> list = queryByKeyword(offset, pageSize, userId, keyword);
        page.setList(list);
    }

    private int sizeByKeyword(int userId, String keyword) throws SQLException {
        System.out.println("4");
        String sql = "SELECT COUNT(*) FROM cost WHERE user_id = '"+userId+"' AND (name LIKE '%"+keyword+"%' OR money LIKE '%"+keyword+"%'OR date LIKE '%"+keyword+ "%')";

                Connection conn = DBUtils.getConn();
                PreparedStatement pst = conn.prepareStatement(sql);
                ResultSet rs = pst.executeQuery();
                if (rs.next()) {
                    System.out.println("5");
                    System.out.println(rs.getInt(1));

                    return rs.getInt(1);
                }


        return 0;
    }

    private List<Cost> queryByKeyword(int offset, int limit, int userId, String keyword) throws SQLException {
        System.out.println("7");
        String sql = "select * from cost WHERE user_id = '"+userId+"' and (name LIKE '%"+keyword+"%' OR money LIKE '%"+keyword+"%'OR date LIKE '%"+keyword+ "%')"+" LIMIT "+offset+", "+limit;
        List<Cost> list = new ArrayList<>();
            Connection conn = DBUtils.getConn();
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                Double money = rs.getDouble("money");
                Date date = rs.getDate("date");
                Cost cost = new Cost(id, name, money, date);
                list.add(cost);
            }
        System.out.println(list);
        return list;
    }
}
